﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using KTool2.Exceptions;

namespace KTool2.Database
{
    class DBManagement
    {
        private static string dataSource = null;

        public static string DataSource
        {
            get
            {
                if (dataSource == null)
                {
                    dataSource = new OleDbConnection(KTool2.Common.Constant.CONNECTION_STRING).DataSource;
                }

                return DBManagement.dataSource;
            }
        }

        public static OleDbConnection OpenedConnection
        {
            get
            {
                if (!System.IO.File.Exists(DataSource))
                {
                    throw new DBException(0, null);
                }

                OleDbConnection con = new OleDbConnection(KTool2.Common.Constant.CONNECTION_STRING);

                try
                {
                    con.Open();
                }
                catch (OleDbException e)
                {
                    if (e.ErrorCode == -2147467259) // Message=The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.
                    {
                        throw new DBException(1, null);
                    }
                    else // unexpected error
                    {
                        throw new DBException(-1, null);
                    }
                }

                return con;
            }
        }

        /// <summary>
        /// Calling method must close the reader itself.
        /// </summary>
        /// <param name="cmd">Command that already has database connection as well as sql command</param>
        /// <returns>OleDbDataReader instance</returns>
        public static OleDbDataReader ExcuteReader(OleDbCommand cmd)
        {
            try
            {
                return cmd.ExecuteReader();
            }
            catch (OleDbException e)
            {
                if (e.ErrorCode == -2147217865) // Cannot find sheet name
                {
                    string notFoundSheet = e.Message.Substring(e.Message.IndexOf("'") + 1, e.Message.LastIndexOf("'") - e.Message.IndexOf("'") - 1);
                    throw new DBException(2, notFoundSheet);
                }
                else // unexpected error
                {
                    throw new DBException(-1, null);
                }
            }
        }

        public static int ExecuteNonQuery(OleDbCommand cmd)
        {
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (OleDbException e)
            {
                if (e.ErrorCode == -2147217865) // Cannot find sheet name
                {
                    string notFoundSheet = e.Message.Substring(e.Message.IndexOf("'") + 1, e.Message.LastIndexOf("'") - e.Message.IndexOf("'") - 1);
                    throw new DBException(2, notFoundSheet);
                }
                else // unexpected error
                {
                    throw new DBException(-1, null);
                }
            }
        }

        /// <summary>
        /// Get list of table in database/excel file.
        /// </summary>
        /// <returns>List of table in database/excel file</returns>
        public static string[] TableArray
        {
            get
            {
                string[] tableArray = null;

                using (OleDbConnection connection = DBManagement.OpenedConnection)
                using (System.Data.DataTable dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                {
                    tableArray = new String[dataTable.Rows.Count];
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        tableArray[i] = dataTable.Rows[i]["TABLE_NAME"].ToString();
                    }
                }

                return tableArray;
            }
        }

        public static bool IsDBContains(string sheetName)
        {
            string[] tableArray = DBManagement.TableArray;

            foreach (string table in tableArray)
            {
                if (table.Replace('#', '.').Equals(sheetName))
                {
                    return true;
                }
            }

            return false;
        }
    }
}
